package com.express.dao.impl;

import com.express.bean.Express;
import com.express.dao.BaseExpressDao;
import com.express.exception.DuplicateCodeException;
import com.express.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: laixiangdong
 * @Date 2020/12/06
 */
public class ExpressDaoMysql implements BaseExpressDao {
    // 查询数据库中所有的快递的总数及新增，待取件快递的总数及新增
    private static final String SQL_CONSOLE = "SELECT COUNT( id ) data1_size, COUNT( TO_DAYS( intime )= TO_DAYS( NOW()) OR NULL) data1_day, count( status = 0 OR NULL ) data2_size, COUNT( TO_DAYS( intime )= TO_DAYS( NOW()) AND STATUS = 0 OR NULL) data2_day FROM express";
    // 查询所有快递信息
    private static final String SQL_FIND_ALL = "SELECT * FROM express";
    // 分页查询快递信息
    private static final String SQL_FIND_LIMIT = "SELECT * FROM express LIMIT ?, ?";
    // 通过单号查询快递
    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM express WHERE number = ?";
    // 通过取件码查询快递
    private static final String SQL_FIND_BY_CODE = "SELECT * FROM express WHERE code = ?";
    // 通过手机号查询快递
    private static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM express WHERE userphone = ?";
    // 通过录入人手机查询快递
    private static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM express WHERE sysphone = ?";
    // 新增
    private static final String SQL_INSERT = "INSERT INTO express(number, username, userphone, company, code, intime, status, sysphone) VALUES (?, ?, ?, ?, ?, NOW(), 0, ?)";
    // 更新
    private static final String SQL_UPDATE = "UPDATE express SET number = ?, username = ?, company = ?, status = ? WHERE id = ?";
    // 删除
    private static final String SQL_DELETE = "DELETE FROM express WHERE id = ?";
    // 取件
    private static final String SQL_UPDATE_STATUS = "UPDATE express SET status = 1, outtime = NOW(), code = NULL WHERE code = ?";
    // 通过
    private static final String SQL_FIND_BY_ID = "SELECT * FROM express WHERE id = ?";
    // 用户数
    private static final String SQL_USER_CONSOLE_SIZE = "SELECT COUNT(DISTINCT userphone) userdatasize FROM express";
    private static final String SQL_USER_CONSOLE_DAY = "SELECT COUNT(DISTINCT userphone) userdataDay FROM express WHERE DATEDIFF(intime,NOW()) = 0";
    private static final String SQL_FIND_BY_USERPHONE_AND_STATUS = "SELECT * FROM express WHERE userphone = ? and status = ?";

    /**
     * 用于查询数据库中所有的快递的总数及新增，待取件快递的总数及新增
     *
     * @return [{size:快递总数,day:今日新增}, {size:待取件总数,day:今日新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String, Integer>> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            result = state.executeQuery();
            if (result.next()) {
                int data1_size = result.getInt("data1_size");
                int data1_day = result.getInt("data1_day");
                int data2_size = result.getInt("data2_size");
                int data2_day = result.getInt("data2_day");
                Map data1 = new HashMap();
                data1.put("data1_size", data1_size);
                data1.put("data1_day", data1_day);
                Map data2 = new HashMap();
                data2.put("data2_size", data2_size);
                data2.put("data2_day", data2_day);
                data.add(data1);
                data.add(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    /**
     * 用于查询数据库中快递用户的信息总数及新增
     *
     * @return
     */
    @Override
    public Map<String, Integer> userConsole() {
        Map<String, Integer> data = new HashMap<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_USER_CONSOLE_SIZE);
            result = state.executeQuery();
            if (result.next()) {
                int userDataSize = result.getInt("userDataSize");
                data.put("userDataSize", userDataSize);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }

        Connection conn2 = DruidUtil.getConnection();
        PreparedStatement state2 = null;
        ResultSet result2 = null;
        try {
            state2 = conn2.prepareStatement(SQL_USER_CONSOLE_DAY);
            result2 = state2.executeQuery();
            if (result2.next()) {
                int userDataDay = result2.getInt("userDataDay");
                data.put("userDataDay", userDataDay);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    /**
     * @param limit      分页标记，true 表示分页
     * @param offset     sql 起始索引
     * @param pageNumber 每页查询数
     * @return 快递集合
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Express> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            if (limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            } else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            result = state.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String number = result.getString("number");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                Express express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    /**
     * 用于通过快递单号查询快递
     *
     * @param number 快递单号
     * @return 快递对象
     */
    @Override
    public Express findByNumber(String number) {
        Express express = null;
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_NUMBER);
            state.setString(1, number);
            result = state.executeQuery();
            if (result.next()) {
                int id = result.getInt("id");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return express;
    }

    /**
     * 用于通过快递验证码查询快递
     *
     * @param code 验证码
     * @return 快递对象
     */
    @Override
    public Express findByCode(String code) {
        Express express = null;
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_CODE);
            state.setString(1, code);
            result = state.executeQuery();
            if (result.next()) {
                int id = result.getInt("id");
                String number = result.getString("number");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");

                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return express;
    }

    /**
     * 用于通过快递id查询快递
     *
     * @param id 快递id
     * @return 快递对象
     */
    @Override
    public Express findByID(int id) {
        Express express = null;
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_ID);
            state.setInt(1, id);
            result = state.executeQuery();
            if (result.next()) {
                String number = result.getString("number");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return express;
    }

    /**
     * 用于通过用户手机号查询所有快递
     *
     * @param userPhone 用户手机号
     * @return 快递集合
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        ArrayList<Express> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            state.setString(1, userPhone);
            result = state.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String number = result.getString("number");
                String userName = result.getString("username");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysphone");
                Express express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        ArrayList<Express> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE_AND_STATUS);
            state.setString(1, userPhone);
            state.setInt(2,status);
            result = state.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String number = result.getString("number");
                String userName = result.getString("username");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                String sysPhone = result.getString("sysphone");
                Express express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    /**
     * 用于通过录入人员手机号查询所有快递
     *
     * @param sysPhone 录入人手机号
     * @return 快递集合
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        ArrayList<Express> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_SYSPHONE);
            state.setString(1, sysPhone);
            result = state.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String number = result.getString("number");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("intime");
                Timestamp outTime = result.getTimestamp("outtime");
                int status = result.getInt("status");
                Express express = new Express(id, number, userName, userPhone, company, code, inTime, outTime, status, sysPhone);
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    /**
     * 用于添加快递
     *
     * @param express 快递对象
     * @return true 表示添加成功
     */
    @Override
    public Boolean insert(Express express) throws DuplicateCodeException {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            // INSERT INTO express(number, username, userphone, company, code, intime, status, sysphone) VALUES (?, ?, ?, ?, ?, NOW(), 0, ?)
            state.setString(1, express.getNumber());
            state.setString(2, express.getUserName());
            state.setString(3, express.getUserPhone());
            state.setString(4, express.getCompany());
            state.setString(5, express.getCode());
            state.setString(6, express.getSysPhone());
            int i = state.executeUpdate();
            return i > 0;
        } catch (SQLException e) {
            // 取件码重复处理
            if (e.getMessage().endsWith("for key 'code'")) {
                throw new DuplicateCodeException(e.getMessage());
            } else {
                e.printStackTrace();
            }
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }

    /**
     * 用于通过快递id更新快递信息
     *
     * @param id         快递id
     * @param newExpress 新的快递对象
     * @return true 表示更新成功
     */
    @Override
    public Boolean update(int id, Express newExpress) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            // UPDATE express SET number = ?, username = ?, company = ?, status = ? WHERE id = ?
            state.setString(1, newExpress.getNumber());
            state.setString(2, newExpress.getUserName());
            state.setString(3, newExpress.getCompany());
            state.setInt(4, newExpress.getStatus());
            state.setInt(5, id);
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }

    /**
     * 用于通过快递id删除快递
     *
     * @param id 快递id
     * @return true 表示删除成功
     */
    @Override
    public Boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1, id);
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }

    /**
     * 用于取件时更新快递状态
     *
     * @param code 单号
     * @return true 表示取件成功
     */
    @Override
    public Boolean updateStatus(String code) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_STATUS);
            state.setString(1, code);
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }
}
